//
//  InitDbTableQuality.m
//  NursingManages
//
//  Created by HuangZhen on 2017/6/6.
//  Copyright © 2017年 huangzhen. All rights reserved.
//

#import "InitDbTableQuality.h"
#import "Model_Hr_Ns_Inspect_New.h"
#import "Model_Hr_InspectDetail_New.h"
#import "DBHelper.h"
#import "HQc_codeModel.h"
#import "Model_Hr_Satisfaction.h"
#import "Model_Hr_InspectNew.h"
#import "Model_Hr_CodeReasonModel.h"
@implementation InitDbTableQuality

-(NSString*) GetMainTableSql{
     
    
    NSString *sql = @"create table if not exists hr_ns_inspect_new\
    (\
     result_id            text not null,\
     level_id             text not null,\
     code_id              text not null,\
     type_id              text,\
     standard_id          text,\
     dept_id              text,\
     check_date           text,\
     check_date2          text,\
     check_date3          text,\
     round_hours          text,\
     check_obj            text,\
     nurse_id             text,\
     nurse_name           text,\
     teachenus_id         text,\
     teachenus_name       text,\
     checker_id           text,\
     checker_name         text,\
     score                numeric,\
     resul                text,\
     char1                text,\
     char2                text,\
     char3                text,\
     num1                 numeric,\
     num2                 numeric,\
     num3                 numeric,\
     date1                text,\
     date2                text,\
     date3                text,\
     remarks              text,\
     input_user_id        text,\
     input_user_name      text,\
     input_date           text,\
     modify_user_id       text,\
     modify_user_name     text,\
     modify_date          text,\
     othher_chaeck_id     text,\
     othher_chaeck_name   text,\
     other_checktype      text,\
     check_status         numeric,\
     handwrite_checkentry text,\
     responsible_id       text,\
     responsible_name     text,\
     responsible_type     text,\
     point_record         text,\
     issubmit             numeric,\
     resion_id            text,\
     delere_flag          numeric,\
     head_nurse_name      text,\
     patient_totalnum     numeric,\
     patient_careofone    numeric,\
     evaluation           text,\
     solve_prob_plan      text,\
     plan_id              text,\
     ns_name              text,\
     hosp_num             text,\
     bed_num              text,\
     upload_flag          text\
     )";
    

    
    return sql;
}
-(NSString*) GetDetailTableSql{

    return @"create table if not exists hr_ns_inspectdetail_new\
    (\
     result_id        text not null,\
     code_id          text not null,\
     score            numeric,\
     resion           text,\
     responsible_id   text,\
     responsible_name text,\
     responsible_type text,\
     ingore_flag      numeric,\
     one_flag         numeric,\
     prize            numeric,\
     penalty          numeric,\
     frequency        numeric,\
     resion_id        text,\
     par_id           text,\
     check_flag       numeric,\
     upload_flag      text\
     )";
}


-(NSMutableArray *) TransalteToModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Ns_Inspect_New  *model = [[Model_Hr_Ns_Inspect_New alloc]init];
        model.RESULT_ID = [set stringForColumn:@"result_id"];
        model.LEVEL_ID = [set stringForColumn:@"level_id"];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.DEPT_ID = [set stringForColumn:@"dept_id"];
        model.CHECK_DATE = [set stringForColumn:@"check_date"];
        model.CHECKER_ID = [set stringForColumn:@"checker_id"];
        model.CHECKER_NAME = [set stringForColumn:@"checker_name"];
        model.SCORE = [set stringForColumn:@"score"];
        model.RESUL = [set stringForColumn:@"resul"];
        model.REMARKS = [set stringForColumn:@"remarks"];
        model.INPUT_USER_ID = [set stringForColumn:@"input_user_id"];
        model.INPUT_USER_NAME = [set stringForColumn:@"input_user_name"];
        model.INPUT_DATE = [set stringForColumn:@"input_date"];
        model.OTHHER_CHAECK_ID = [set stringForColumn:@"othher_chaeck_id"]?:@"";
        model.UPLOAD_FLAG = [[set stringForColumn:@"upload_flag"]integerValue];
        model.OTHHER_CHAECK_NAME = [set stringForColumn:@"othher_chaeck_name"]?:@"";
        model.CHECK_STATUS = [[set stringForColumn:@"check_status"]integerValue];
        model.ISSUBMIT = [[set stringForColumn:@"issubmit"]integerValue];
        model.DELERE_FLAG = [[set stringForColumn:@"delere_flag"]integerValue];
        model.NURSE_ID = [set stringForColumn:@"nurse_id"];
        model.NURSE_NAME = [set stringForColumn:@"nurse_name"];
        model.ROUND_HOURS = [set stringForColumn:@"round_hours"];
        [array addObject:model];
        
    }
    
    return array;
}


-(NSMutableArray *) detailTransalteToModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_InspectDetail_New  *model = [[Model_Hr_InspectDetail_New alloc]init];
        model.RESULT_ID = [set stringForColumn:@"result_id"];
        model.SCORE = [[set stringForColumn:@"score"]doubleValue];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.RESION = [set stringForColumn:@"resion"];
        model.RESPONSIBLE_ID = [set stringForColumn:@"responsible_id"];
        model.RESPONSIBLE_NAME = [set stringForColumn:@"responsible_name"];
        model.RESPONSIBLE_TYPE = [set stringForColumn:@"responsible_type"];
        model.INGORE_FLAG = [[set stringForColumn:@"ingore_flag"]doubleValue];
        model.ONE_FLAG = [[set stringForColumn:@"one_flag"]integerValue];
        model.PRIZE = [[set stringForColumn:@"prize"]doubleValue];
        model.FREQUENCY = [[set stringForColumn:@"frequency"]integerValue];
        model.RESION_ID = [set stringForColumn:@"resion_id"];
        model.PENALTY = [[set stringForColumn:@"penalty"]doubleValue];
        model.par_id = [set stringForColumn:@"par_id"];
        model.check_flag = [[set stringForColumn:@"check_flag"]integerValue];
        [array addObject:model];
        
    }
    
    return array;
}
-(NSMutableArray *) manyidu:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Satisfaction  *model = [[Model_Hr_Satisfaction alloc]init];
        model.SAT_PERIOD = [set stringForColumn:@"sat_period"];
        model.ITEM_CODE = [set stringForColumn:@"item_code"];
        model.ITEM_SCORE = [[set stringForColumn:@"item_score"]integerValue];
        model.DEPT_CODE = [set stringForColumn:@"dept_code"];
        model.TABLE_CODE = [set stringForColumn:@"table_code"];
        model.SAT_DES = [set stringForColumn:@"sat_des"];
        model.SAT_TYPE = [set stringForColumn:@"sat_type"];
        model.DELETE_FLAG = [[set stringForColumn:@"delete_flag"]integerValue];
        model.MODIFIER =[set stringForColumn:@"modifier"];
        model.STATUS = [[set stringForColumn:@"status"]integerValue];
        model.ITEM_ID = [set stringForColumn:@"item_id"];
        model.SAT_DES2 = [set stringForColumn:@"sat_des2"];
        model.MODIFY_ID = [set stringForColumn:@"modify_id"];
        model.TEMP_COL = [set stringForColumn:@"temp_col"];
        model.MODIFY_DATE =[set stringForColumn:@"modify_date"];
        model.STEP_ID = [set stringForColumn:@"step_id"];
        model.ORG_ID = [set stringForColumn:@"org_id"];
        model.SURVEY_DATE = [set stringForColumn:@"survey_date"];
        model.REASONS = [set stringForColumn:@"reasons"];
        model.SAT_ID = [set stringForColumn:@"sat_id"];
        [array addObject:model];
        
        
        
    }
    
    return array;
}


- (NSMutableArray *)GetDetailInspectTableWithResult:(NSString *)result_ID
{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspectdetail_new where result_id = '%@'",result_ID];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self detailTransalteToModel:set];
    [DBHelper.DB close];
    return array;

}


- (NSMutableArray *)GetDetailInspectTableWithResultAndPar_id:(NSString *)result_ID
                                                      par_id:(NSString *)par_id
{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspectdetail_new where result_id = '%@' And par_id = '%@'",result_ID,par_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self detailTransalteToModel:set];
    [DBHelper.DB close];
    return array;

}
- (NSMutableArray *)GetInspectNewTableWithResult:(NSString *)result_ID
{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspect_new where result_id = '%@'",result_ID];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteToModel:set];
    [DBHelper.DB close];
    return array;

}

-(NSMutableArray *) GetMainTableWithUpload_flag:(NSInteger )upload_flag
                                        typy_id:(NSString *)type_id
                                       level_id:(NSString *)level_id
{
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspect_new where upload_flag = '%ld' AND type_id = '%@' AND level_id = '%@'",upload_flag,type_id ,level_id];
    NSLog(@"%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteToModel:set];
    [DBHelper.DB close];
    return array;

}

//操作过程考核查询
- (NSMutableArray *)GetMainTableWithAssessUpload_flag:(NSInteger)upload_flag
                                              typy_id:(NSString *)type_id
                                             level_id:(NSString *)level_id
{
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspect_new where upload_flag = '%ld' AND type_id = '%@' AND level_id = '%@'",upload_flag,type_id,level_id];
    NSLog(@"%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteToModel:set];
    [DBHelper.DB close];
    return array;

}

-(NSMutableArray *) GetDetailIUnUploadTable

{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspectdetail_new where upload_flag = '0'"];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self detailTransalteToModel:set];
    [DBHelper.DB close];
    return array;
    

}


- (NSMutableArray *)GetmanyiduUnUploadTable
{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_satisfaction_detail"];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self manyidu:set];
    [DBHelper.DB close];
    return array;
}



 - (HQc_codeModel *)GetCodeNameWithCodeID:(NSString *)code_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_ns_checkcode where code_id = '%@' ", code_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    HQc_codeModel *model = [[HQc_codeModel alloc]init];

    while ([set next]) {
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.SCORE = [set stringForColumn:@"score"];
        model.STANDARD_SCORE = [set stringForColumn:@"standard_score"];
        model.WEIGHTS = [set stringForColumn:@"weights"];
        model.INSPECTION_METHOD = [set stringForColumn:@"inspection_method"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.OTHERHELP = [set stringForColumn:@"otherhelp"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.SHOW_FRAME = [set stringForColumn:@"show_frame"];
        model.PROBLEM_COLLECTION_FLAG = [set stringForColumn:@"problem_collection_flag"];
        model.KEYCODE_FLAG = [set stringForColumn:@"keycode_flag"];
        
        
    }
    [DBHelper.DB close];
    return model;
}

- (Model_Hr_SatType *)GetHSaNameWithCodeID:(NSString *)code_id
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"select * from hr_sattype where type_id = '%@' ", code_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sqlStr];
    Model_Hr_SatType *model = [[Model_Hr_SatType alloc]init];
    
    while ([set next]) {
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.TYPE_NAME = [set stringForColumn:@"type_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        
    }
    [DBHelper.DB close];
    return model;

}

- (NSMutableArray *)GetCheckDate
{
    [DBHelper.DB open];
    NSString *sqlStr = [NSString stringWithFormat:@"SELECT substr(check_date,0,7) as M from hr_ns_inspect_new group by substr(check_date,0,7) ORDER BY  substr(check_date,0,7) DESC"];
    
    NSString *sql = [sqlStr stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
    NSString *msg = @"";
    
    
    @try {
        
        [DBHelper.DB open];
        if ([[sql lowercaseString] hasPrefix:@"select"]){
            
            FMResultSet *set = [DBHelper.DB executeQuery:sql];
            NSMutableArray *array = [NSMutableArray array];
            
            
            while ([set next]) {
                
                NSString *row = @"";
                for (int i = 0; i < set.columnCount; i++) {
                    NSString *colName = [set columnNameForIndex:i];
                    NSString *colValue = [set stringForColumnIndex:i];
                    NSString *col = [NSString stringWithFormat:@"%@ : %@", colName,colValue];
                    row =  [row isEqualToString:@""] ?
                    [NSString stringWithFormat:@"%@", col] :
                    [NSString stringWithFormat:@"%@ , %@", row, col] ;
                    
                }
                [array addObject:row];
                
            }
            
            msg = [NSString stringWithFormat: @"共查到%lu条记录", (unsigned long)[array count ]];
            return array;

            
        }else{
            
            [DBHelper.DB executeStatements:sql];
            msg = @"执行成功";
        }
        
        
        if ([DBHelper.DB hadError]){
            msg = [DBHelper.DB lastErrorMessage];
        }
    }
    @catch (NSException *exception) {
        msg = [NSString stringWithFormat:@"NSException: %@",exception];
    }
    @finally
    {
        [DBHelper.DB close];
    }
}


- (void)UpdateTableWithResult_id:(NSString *)result_id
{
    [DBHelper.DB open];
    NSString *sql = [NSString stringWithFormat:@"update hr_ns_inspect_new set upload_flag = '1' where upload_flag = '0'"];
    NSString *sql1 = [NSString stringWithFormat:@"update hr_ns_inspectdetail_new set upload_flag = '1' where upload_flag = '0'"];
    BOOL result = [DBHelper.DB executeUpdate:sql];
    BOOL result1 = [DBHelper.DB executeUpdate:sql1];
    NSLog(result ? @"更新成功":@"更新失败");
    NSLog(result1 ? @"更新成功":@"更新失败");
    [DBHelper.DB close];

}

- (void)UpdateTableWithHSaTableCode:(NSString *)table_code
{
    [DBHelper.DB open];
    NSString *sql = [NSString stringWithFormat:@"update hr_satisfaction_detail set upload_flag = '1' where upload_flag = '0'"];
    BOOL result = [DBHelper.DB executeUpdate:sql];
    NSLog(result ? @"更新成功":@"更新失败");
    [DBHelper.DB close];
    

}

- (void)deleteTableWithResult_id:(NSString *)result_id
{
    [DBHelper.DB open];
    NSString *sql = [NSString stringWithFormat:@"delete from hr_ns_inspect_new where result_id = '%@'",result_id];
    NSString *sql1 = [NSString stringWithFormat:@"delete from hr_ns_inspectdetail_new where result_id = '%@'",result_id];
    
    NSString *sql2 = [NSString stringWithFormat:@"delete from hr_ns_inspectnew_pointdet where result_id = '%@'",result_id];
    BOOL result = [DBHelper.DB executeUpdate:sql];
    BOOL result1 = [DBHelper.DB executeUpdate:sql1];
    BOOL result2 = [DBHelper.DB executeUpdate:sql2];

    NSLog(result ? @"删除成功":@"删除失败");
    NSLog(result1 ? @"删除成功":@"删除失败");
    NSLog(result2 ? @"删除成功":@"删除失败");

    [DBHelper.DB close];

}

- (void)deleteTableWithHSaTableCode:(NSString *)table_code
{
    [DBHelper.DB open]; 
    NSString *sql = [NSString stringWithFormat:@"delete from hr_satisfaction_detail where table_code = '%@'",table_code];
    BOOL result = [DBHelper.DB executeUpdate:sql];
    NSLog(result ? @"删除成功":@"删除失败");
    [DBHelper.DB close];

}

- (NSMutableArray *)GetInspextNewWithReslut_id:(NSString *)reslut_id code_id:(NSString *)code_id
{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspectnew_pointdet where result_id = '%@' AND code_id = '%@'",reslut_id,code_id];
    NSLog(@"===%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self inspectModel:set];
    [DBHelper.DB close];
    return array;

}

- (NSMutableArray *)getInspextNewWithReslut_id:(NSMutableArray *)reslut_id {
    
    [DBHelper.DB open];
    NSMutableArray *array = [NSMutableArray array];

    for (int i = 0; i < reslut_id.count; i ++) {
        
        Model_Hr_Ns_Inspect_New *model = reslut_id[i];
        NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspectnew_pointdet where result_id = '%@'",model.RESULT_ID];
        
        FMResultSet *set = [DBHelper.DB executeQuery:sql];
        
        while ([set next]) {
            Model_Hr_InspectNew *model = [[Model_Hr_InspectNew alloc]init];
            model.RESULT_ID = [set stringForColumn:@"result_id"];
            model.CONTENT_ID = [set stringForColumn:@"content_id"];
            model.CODE_ID = [set stringForColumn:@"code_id"];
            model.BUCKLESCORE = [set stringForColumn:@"bucklescore"];
            model.QUANTITY = [set stringForColumn:@"quantity"];
            model.ITEM_ID = [set stringForColumn:@"item_id"];
            [array addObject:model];
        }

    }
    [DBHelper.DB close];
    return array;

}
//    NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_inspectnew_pointdet where result_id = '%@'",reslut_id];
//    FMResultSet *set = [DBHelper.DB executeQuery:sql];
//    NSMutableArray *array = [self inspectModel:set];
//    [DBHelper.DB close];
//    return array;



-(NSMutableArray *) inspectModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_InspectNew  *model = [[Model_Hr_InspectNew alloc]init];
        model.RESULT_ID = [set stringForColumn:@"result_id"];
        model.CONTENT_ID = [set stringForColumn:@"content_id"];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.BUCKLESCORE = [set stringForColumn:@"bucklescore"];
        model.QUANTITY = [set stringForColumn:@"quantity"];
        model.ITEM_ID = [set stringForColumn:@"item_id"];
        [array addObject:model];
    }
    
    return array;

}


- (NSMutableArray *)getChooseReasonsWithCode_id:(NSString *)code_id
{
    
    [DBHelper.DB open];
    
    NSMutableArray *array = [NSMutableArray array];
    
    
        NSString* sql = [NSString stringWithFormat:@"select * from hr_ns_code_penaltyreason where code_id = '%@'",code_id];
        
        FMResultSet *set = [DBHelper.DB executeQuery:sql];

        while ([set next]) {
            Model_Hr_CodeReasonModel *model = [[Model_Hr_CodeReasonModel alloc]init];
            model.code_id = [set stringForColumn:@"code_id"];
            model.reason_id = [set stringForColumn:@"reason_id"];
            model.reason_name = [set stringForColumn:@"reason_name"];
            model.use_count = [set stringForColumn:@"use_count"];
            model.score = [set stringForColumn:@"score"];
            model.order_num = [set stringForColumn:@"order_num"];
            model.standard_flag = [set stringForColumn:@"standard_flag"];
            [array addObject:model];
        }
        
    
    [DBHelper.DB close];
    
    return array;

}


@end
